﻿Imports System.Data
Imports System.Data.OleDb
Imports DTO

Public Class XeDao
    Dim provider As New DataProvider

    Public Function LayDanhSachXe() As DataTable
        Dim sql As String = "SELECT * FROM Xe"

        Return provider.ThucThiCauTruyVan(sql)


    End Function

    Public Function TimXe(ByVal xe As XeDto) As DataTable
        Dim sql As String = "SELECT * FROM Xe WHERE 1 "
        'If xe.MaXe <> 0 Then
        '    sql += " and MaXe =" & xe.MaXe
        'End If
        If xe.SoXe <> "" Then
            sql += " and SoXe like '" & xe.SoXe & "%' "
        End If
        If xe.HieuXe <> "" Then
            sql += " and HieuXe like'" & xe.HieuXe & "%' "
        End If
        If xe.DoiXe <> 0 Then
            sql += " and DoiXe =" & xe.DoiXe
        End If
        If xe.SoChoNgoi <> 0 Then
            sql += " and SoChoNgoi = " & xe.SoChoNgoi
        End If

        Return provider.ThucThiCauTruyVan(sql)

    End Function

    Public Function TimXeTheoMaXe(ByVal maXe As Integer) As DataTable
        Dim sql As String = "SELECT * FROM Xe WHERE MaXe = " & maXe

        Return provider.ThucThiCauTruyVan(sql)

    End Function

    Public Function LaySoChoNgoi_TheoMaXe(ByVal maXe As Integer) As Integer
        Dim soChoNgoi As Integer = 0

        Dim dt As DataTable = TimXeTheoMaXe(maXe)
        If dt.Rows.Count > 0 Then
            soChoNgoi = dt.Rows(0).Item("SoChoNgoi")
        End If
        Return soChoNgoi
    End Function

    Public Function ThemXe(ByVal xe As XeDto) As Integer
        Dim sql As String = "INSERT  INTO XE(SoXe, HieuXe, DoiXe, SoChoNgoi) VALUES (?, ?, ?, ?)"

        Dim dsParameter As New List(Of OleDbParameter)
        dsParameter.Add(New OleDbParameter("@SoXe", xe.SoXe))
        dsParameter.Add(New OleDbParameter("@HieuXe", xe.HieuXe))
        dsParameter.Add(New OleDbParameter("@DoiXe", xe.DoiXe))
        dsParameter.Add(New OleDbParameter("@SoChoNgoi", xe.SoChoNgoi))

        Return provider.ThucThiTruyVanThuong(sql, dsParameter)
    End Function

    Public Function CapNhatXe(ByVal xe As XeDto) As Integer
        Dim sql As String = "UPDATE XE SET SoXe = ?, HieuXe = ?, DoiXe = ?, SoChoNgoi = ? WHERE MaXe = ?"

        Dim dsParameter As New List(Of OleDbParameter)
        dsParameter.Add(New OleDbParameter("@SoXe", xe.SoXe))
        dsParameter.Add(New OleDbParameter("@HieuXe", xe.HieuXe))
        dsParameter.Add(New OleDbParameter("@DoiXe", xe.DoiXe))
        dsParameter.Add(New OleDbParameter("@SoChoNgoi", xe.SoChoNgoi))
        dsParameter.Add(New OleDbParameter("@MaXe", xe.MaXe))

        Return provider.ThucThiTruyVanThuong(sql, dsParameter)
    End Function

    Public Function XoaXe(ByVal maCanXoa As Long) As Integer
        Dim sql As String = "DELETE FROM XE WHERE MaXe = ?"

        Dim dsParameter As New List(Of OleDbParameter)
        dsParameter.Add(New OleDbParameter("@MaXe", maCanXoa))

        Return provider.ThucThiTruyVanThuong(sql, dsParameter)
    End Function

End Class
